package app.netant.galgame.sayfire;

import org.apache.ecs.xhtml.map;

import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.DBManager;
import cn.lily.com.db.sql.manager.DBOfflineManager;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.io.ExcelReader;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.lang.Basic;

/**
 * Say花火分析文件
 * @author JiaJ
 * @date 2018年6月20日
 *
 */
public class SayFireAnalyse {
	
	private ExcelReader er;
	
	private ExcelWriter ew;
	private DBManager dbManager = DBOfflineManager.getDBManager();
	public void analyse(){
		
		this.er = new ExcelReader("D:/777部汉化GAL整理表180415 .xls");
		this.ew = new ExcelWriter("D:/sayFire.xlsx");
		this.ew.inst();
		this.er.locateSheet(0);
		this.ew.locateSheet(0);
		
		int maxRow = this.er.getRowNum()[1];
		
		int rowI = 0;
		
		for(int r=9;r<=maxRow;r++){
			this.er.locateRow(r);
			
			String name = this.er.getCellValue(2);
			String href = this.er.getCellValue(4);
			
			if(href == null || "NULL".equals(href)){
				href = "";
			}
			
			if(name == null || "NULL".equals(name)){
				continue;
			}else{
				
				this.ew.locateRow(rowI);
				
				name = name.replaceAll("'", "").replaceAll("\"", "");
				
				this.ew.setCellValue(0,name);
				
				System.out.println(name);
				
				String sqlString = "select RES_ID,GAME_ID,GAME_NAME,GAME_TYP,GAME_LANG,"
						+ "GAME_VISUAL,GAME_SIZE,GAME_SIZE_NUM,GAME_FACTORY,GAME_FIRST,"
						+ "GAME_INTRU,GEME_MEMO,GAME_NAME_PLUS,"
						+ "DISK_ID,GAME_STORE_PATH,GAME_TAGS,"
						+ "to_char(GAME_PRO_TIME,'yyyy-mm-dd hh24:mi:ss') GAME_PRO_TIME,"
						+ "to_char(GAME_CRE_TIME,'yyyy-mm-dd hh24:mi:ss') GAME_CRE_TIME "
				 + "from ${schema}.LEO_GALGAME_INFO "
				 + "where 1=1 and ( upper(GAME_NAME) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "' or upper(GAME_NAME_PLUS) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "') "
				 +" order by GAME_CRE_TIME desc";
				
				DataTable dataTable = dbManager.executeQueryTable(sqlString);
				
				String newName = "";			
				while(dataTable.next()){
					newName += ","+ dataTable.getString("GAME_NAME");
					if(dataTable.getString("GAME_NAME_PLUS") != null && !"".equals(dataTable.getString("GAME_NAME_PLUS") )){
						newName +=","+dataTable.getString("GAME_NAME_PLUS");
					}
				}
				
				this.ew.setCellValue(1,newName);
				
				this.ew.setCellValue(2,href);
				
				rowI++;
				
			}
			
			
			
		}
		
		this.ew.write();
		
		
		
	}
	
	public static void main(String[] args) {
		
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		
		SayFireAnalyse anl = new SayFireAnalyse();
		anl.analyse();
	}
	
}
